package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.jdbc.OracleDriver;

public class oracle{
    public static void main(String[] args){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        PreparedStatement insTab = null;
        try{
            DriverManager.registerDriver(new OracleDriver());
            conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@//127.0.0.1:1521/xe", "kozerskil",
                "kozerskil");//dane zapisane w tnsnames.ora   

            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from testowa");
            System.out.print("| ");
            while(rs.next()){
                int x = rs.getInt("id");
                int y = rs.getInt("value");
                System.out.print(x + "," + y + " | ");
            }
            conn.setAutoCommit(false);
            try{
                for(int i = 1; i < 30; i++){
                    insTab = conn
                        .prepareStatement("insert into testowa values (?,?,?)");
                    insTab.setInt(1, i);
                    String tekst = "koto" + (int)Math.pow(i, 3);
                    insTab.setString(2, tekst);
                    insTab.setInt(3, tekst.length());
                    insTab.executeUpdate();
                }
                conn.commit();
                System.out.print("\n| ");
                rs = stmt.executeQuery("select * from testowa");
                while(rs.next()){
                    int x = rs.getInt("id");
                    int y = rs.getInt("value");
                    System.out.print(x + "," + y + " | ");
                }
            }catch(Exception ex){
                conn.rollback();
                System.out.print("\nRollback\n");
            }finally{
                conn.setAutoCommit(true);
            }
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }finally{
            if(insTab != null){
                try{
                    insTab.close();
                }catch(SQLException sqlEx){}
                insTab = null;
            }
            if(rs != null){
                try{
                    rs.close();
                }catch(SQLException sqlEx){}
                rs = null;
            }
            if(stmt != null){
                try{
                    stmt.close();
                }catch(SQLException sqlEx){}
                stmt = null;
            }
            java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM,
                java.util.Locale.UK).format(new java.util.Date());
            if(conn != null){
                try{
                    conn.close();
                }catch(SQLException sqlEx){}
                conn = null;
            }
        }
    }
}
